#!/usr/bin/python
#-*- coding:utf-8 -*-

# activity.py
#
# Copyright (c) 2007 Marcelo Lira dos Santos
#
# Author: Marcelo Lira dos Santos <setanta@gmail.com>
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License as
# published by the Free Software Foundation; either version 2 of the
# License, or (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307
# USA

import os
import gobject
from pysqlite2 import dbapi2 as sqlite

class Activity(gobject.GObject):
    def __init__(self, title=None, author=None, description=None, age=None, 
            duration=None, goals=None, procedures=None, evaluation=None,
            program=None, category=None):

        gobject.GObject.__init__(self)

        self.id = 0
        self.title = title
        self.author = author
        self.description = description
        self.age = age
        self.duration = duration
        self.goals = goals
        self.procedures = procedures
        self.evaluation = evaluation
        self.program = program
        self.category = category


class ActivityDB:
    def __init__(self, dbfile):
        self.dbfile = dbfile
        self.activities = []
        self.tags = set()
        self.connection = sqlite.connect(self.dbfile)


    def get_all_activities(self):
        query = 'SELECT * FROM activities ORDER BY title'
        cursor = self.connection.cursor()
        cursor.execute(query)

        results = []
        for item in cursor.fetchall():
            results.append(self._assemble_object(item))

        return results


    def get_all_categories(self):
        query = 'SELECT * FROM categories ORDER BY name'
        cursor = self.connection.cursor()
        cursor.execute(query)

        return cursor.fetchall()


    def search(self, wordset):
        if not wordset:
            return

        results = set()
        cursor = self.connection.cursor()

        #procura em title
        title_search = "SELECT id FROM activities WHERE"
        first = True
        for word in wordset:
            if not first:
                title_search = title_search + " and"
            else:
                first = False
            title_search = title_search + " title like '%%%s%%'" % word
        #print 'title......: ' + title_search
        cursor.execute(title_search)
        title_results = cursor.fetchall()
        #print title_results
        for item in title_results:
            results.add(item[0])

        #procura em description
        desc_search = "SELECT id FROM activities WHERE"
        first = True
        for word in wordset:
            if not first:
                title_search = title_search + " and"
            else:
                first = False
            desc_search = desc_search + " description like '%%%s%%'" % word
        #print 'description: ' + desc_search
        cursor.execute(desc_search)
        desc_results = cursor.fetchall()
        #print desc_results
        for item in desc_results:
            results.add(item[0])

        #procura em tags
        tag_search = "SELECT a.id FROM activities a, tags t, activities_tags at"\
                    " WHERE at.tag=t.id AND a.id=at.activity"
        for word in wordset:
            tag_search = tag_search + " and t.text like '%%%s%%'" % word
        #print 'tags.......: ' + tag_search
        cursor.execute(tag_search)
        tag_results = cursor.fetchall()
        #print tag_results
        for item in tag_results:
            results.add(item[0])

        return list(results)


    def _assemble_object(self, row):
        ac = Activity()
        ac.id, ac.title, ac.author, ac.description, ac.age, \
        ac.duration, ac.goals, ac.procedures, ac.evaluation, \
        ac.program, ac.category = row

        return ac

if __name__ == '__main__':
    a = ActivityDB()
    print a.get_all_categories()
    print '--'
    print a.get_all_activities()
